import {SqlDB} from "./SqlDB";
import {jsonCopy, mixedObj} from "../function/objFunc";
export namespace SqlTable {
    export interface BaseData extends SqlDB.BaseData {
    }
    export type Operation = "SELECT" | "UPDATE" | "INSERT" | "DELETE";
}

export class SqlTable<TableData extends SqlTable.BaseData> extends SqlDB<TableData> {
    public readonly tableName: string;
    protected defaultData: Partial<TableData>;
    protected whereList: string[] = [];
    protected orList: string[] = [];
    protected updateList: string[] = [];
    protected insertList: string[] = [];
    protected groupRefCols: string[] = [];
    protected orderRefCols: string[] = [];
    protected limitSize: number = 0;
    protected rowOffset: number = 0;
    protected selectAttrs: Array<keyof TableData> = [];
    private sqlType: SqlTable.Operation = "SELECT";
    constructor(dbName: string, tableName: string) {
        super(dbName);
        this.tableName = tableName;
    }
    public insertByList(list: Array<Partial<TableData>>, promiseType?: "all"): Promise<void[]>;
    public insertByList(list: Array<Partial<TableData>>, promiseType: "race"): Promise<void>;
    public insertByList(
        list: Array<Partial<TableData>>,
        promiseType: "all" | "race" = "all"
    ): Promise<void[]> | Promise<void> {
        const promiseList: Array<Promise<void>> = [];
        this.parallelize(() => {
            list.forEach((item) => {
                promiseList.push(this.insert(item));
            });
        });
        if (promiseType === "race") {
            return Promise.race(promiseList);
        }
        return Promise.all(promiseList);
    }
    public insertDirect(data: Partial<TableData>): Promise<void>;
    public insertDirect(data: keyof TableData, val: TableData[keyof TableData]): Promise<void>;
    public insertDirect(data: Partial<TableData> | keyof TableData, val?: TableData[keyof TableData]): Promise<void> {
        let obj: Partial<TableData> = {};
        if (typeof data === "object") {
            obj = data;
        } else {
            obj[data] = val;
        }
        const colNames = Object.keys(obj);
        const colValList = Object.values(obj).map((item: string | number) => {
            return SqlDB.typeVal(item);
        });
        const sql = `INSERT INTO ${this.tableName} (${colNames.join(",")}) VALUES (${colValList.join(",")})`;
        return this.runBySql(sql);
    }
    public updateDirect(data: Partial<TableData>, where: Partial<TableData>): Promise<void>;
    public updateDirect(
        data: keyof TableData,
        val: TableData[keyof TableData],
        where: Partial<TableData>
    ): Promise<void>;
    public updateDirect(
        data: Partial<TableData> | keyof TableData,
        val?: TableData[keyof TableData] | Partial<TableData>,
        where?: Partial<TableData>
    ): Promise<void> {
        let obj: Partial<TableData> = {};
        if (typeof data === "object") {
            obj = data;
            where = val as Partial<TableData>;
        } else {
            obj[data] = val as TableData[keyof TableData];
        }
        const coupleList: string[] = SqlDB.getCoupleList(obj);
        const whereList: string[] = SqlDB.getCoupleList(where);
        let sql = `UPDATE ${this.tableName} SET ${coupleList.join(",")}`;
        if (whereList.length) {
            sql += ` WHERE ${whereList.join(" AND ")}`;
        }
        return this.runBySql(sql);
    }
    public saveData(data: Partial<TableData>, mainKey: keyof TableData | Array<keyof TableData> = "id"): Promise<void> {
        if (typeof mainKey === "string") {
            return this.saveDataBySingleKey(data, mainKey);
        }
        return this.saveDataByKeyList(data, mainKey as Array<keyof TableData>);
    }
    public saveDataByKeyList(data: Partial<TableData>, mainKey: Array<keyof TableData>): Promise<void> {
        const keyList: any = mainKey;
        const whereMap: any = {};
        let hasAllKey = true;
        for (const keyItem of keyList) {
            if (data.hasOwnProperty(keyItem)) {
                whereMap[keyItem] = data[keyItem];
            } else {
                hasAllKey = false;
                break;
            }
        }
        if (hasAllKey) {
            return this.updateDirect(data, whereMap);
        }
        return this.insertDirect(data);
    }
    public saveDataBySingleKey(data: Partial<TableData>, mainKey: keyof TableData = "id"): Promise<void> {
        if (data.hasOwnProperty(mainKey)) {
            const whereObj: any = {};
            whereObj[mainKey] = data[mainKey];
            return this.updateDirect(data, whereObj);
        }
        return this.insertDirect(data);
    }
    public mixedDefaultData(obj: Partial<TableData>): Partial<TableData> {
        if (this.defaultData) {
            return mixedObj(this.defaultData, obj);
        }
        return jsonCopy(obj);
    }
    public getSql(): string {
        let sql = this.sqlType;
        if (this.sqlType === "SELECT") {
            sql += " " + this.selectAttrs.join(", ") + " FROM " + this.tableName;
        } else if (this.sqlType === "UPDATE") {
            sql += " " + this.tableName + " SET " + this.updateList.join(", ");
        } else if (this.sqlType === "DELETE") {
            sql += " FROM " + this.tableName;
        }
        if (this.whereList.length) {
            sql += " WHERE " + this.whereList.join(" AND ");
            if (this.orList.length) {
                sql += " OR ";
            }
        }
        if (this.orList.length) {
            sql += this.orList.join(" OR ");
        }
        if (this.groupRefCols.length) {
            sql += " GROUP BY " + this.groupRefCols.join(", ");
        }
        if (this.orderRefCols.length) {
            sql += " ORDER BY " + this.orderRefCols.join(", ");
        }
        if (this.limitSize) {
            sql += " Limit " + this.limitSize;
            if (this.rowOffset) {
                sql += " OFFSET " + this.rowOffset;
            }
        }
        return sql;
    }
    public select(attrs: Array<keyof TableData> | keyof TableData | "*" = "*"): this {
        this.sqlType = "SELECT";
        this.resetKeyWords();
        if (typeof attrs === "string") {
            this.selectAttrs.push(attrs);
        } else {
            this.selectAttrs = this.selectAttrs.concat(attrs);
        }
        return this;
    }
    public where(data: any, val?: string | number): this {
        if (typeof data === "string") {
            this.whereList.push(data + "=" + SqlDB.typeVal(val));
        } else {
            this.whereList = this.whereList.concat(SqlDB.getCoupleList(data));
        }
        return this;
    }
    public sqlOr(data: any, val?: string | number): this {
        if (typeof data === "string") {
            this.orList.push(data + "=" + SqlDB.typeVal(val));
        } else {
            this.orList = this.orList.concat(SqlDB.getCoupleList(data));
        }
        return this;
    }
    public find(): Promise<Partial<TableData>> {
        const sql = this.getSql();
        return this.getBySql(sql);
    }
    public findAll(): Promise<Array<Partial<TableData>>> {
        const sql = this.getSql();
        return this.getAllBySql(sql);
    }
    public sqlSet(data: Partial<TableData>): this ;
    public sqlSet(data: keyof TableData, val: TableData[keyof TableData]): this ;
    public sqlSet(data: Partial<TableData> | keyof TableData, val?: TableData[keyof TableData]): this {
        if (typeof data === "string") {
            this.updateList.push(data + "=" + SqlDB.typeVal(val));
        } else {
            this.updateList = this.updateList.concat(SqlDB.getCoupleList(data as Partial<TableData>));
        }
        return this;
    }
    public update(): Promise<void> {
        this.sqlType = "UPDATE";
        const sql = this.getSql();
        return this.runBySql(sql);
    }
    public delete(): Promise<void> {
        this.sqlType = "DELETE";
        const sql = this.getSql();
        return this.runBySql(sql);
    }
    public insert(data: Partial<TableData>): Promise<void>;
    public insert(data: keyof TableData, val: TableData[keyof TableData]): Promise<void>;
    public insert(data: Partial<TableData> | keyof TableData, val?: TableData[keyof TableData]): Promise<void> {
        if (typeof data === "string") {
            return this.insertDirect(data as keyof TableData, val);
        }
        return this.insertDirect(data as Partial<TableData>);
    }
    public groupBy(cols: string | string[]): this {
        if (typeof cols === "string") {
            this.groupRefCols.push(cols);
        } else {
            this.groupRefCols = this.groupRefCols.concat(cols);
        }
        return this;
    }
    public orderBy(cols: string | string[]): this {
        if (typeof cols === "string") {
            this.orderRefCols.push(cols);
        } else {
            this.orderRefCols = this.orderRefCols.concat(cols);
        }
        return this;
    }
    public limit(limitSize: number, rowOffset: number = 0): this {
        this.limitSize = limitSize;
        this.rowOffset = rowOffset;
        return this;
    }
    public deleteByKeyList(valList: string[] | number[], key: keyof TableData = "id"): Promise<void> {
        const whereList: string[] = (valList as string[]).map((valItem: string | number) => {
            return `${key}=${SqlDB.typeVal(valItem)}`;
        });
        const sql = `DELETE FROM ${this.tableName} WHERE ${whereList.join(" OR ")}`;
        return this.runBySql(sql);
    }
    protected resetKeyWords() {
        this.selectAttrs = [];
        this.whereList = [];
        this.orList = [];
        this.updateList = [];
        this.insertList = [];
        this.groupRefCols = [];
        this.orderRefCols = [];
        this.limitSize = 0;
        this.rowOffset = 0;
    }
}
